package cn.hls.dao.impl;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.hibernate.Query;
import org.springframework.stereotype.Component;
import org.springframework.util.StringUtils;

import cn.hls.dao.MemberDao;
import cn.hls.entity.CertSet;
import cn.hls.entity.Member;
import cn.hls.entity.vo.PageInfo;
import cn.hls.exception.BussinessException;
import cn.hls.until.GenericsUtils;
import cn.hlsconstant.DataAssessEnum;

 
/**
 *@fileName: MemberDaoImpl.java
 *@package: cn.christ.dao.impl
 *@Description:  人员数据接口实现类
 *@author: yinghui.zhang
 *@email: uuhui@163.com
 *@date: 2015-7-18
 */
@Component("memberDao")
public class MemberDaoImpl extends BaseDaoImpl<Member> implements MemberDao {

	@Override
	public PageInfo<Member> loadMemberInfo(Member entity, PageInfo<Member> page) throws Exception {
		// 定义查询Hql语句
		StringBuilder selectHql=new StringBuilder("from Member p left join  fetch p.company com ");
		// 定义汇总Hql语句
		StringBuilder counthql = new StringBuilder("select count(p.id) from Member p left join  p.company com ");
		//定义查询条件hql
		StringBuilder conditionHql=new StringBuilder("where p.deleted=? ");
		// 定义查询参数
		List<Object> paramList = new ArrayList<Object>();
		paramList.add(false);
		if (entity != null) {
			//按名称查询
			 if(StringUtils.hasText(entity.getName())){
				 conditionHql.append("and p.name like ? ");
				 paramList.add("%"+entity.getName().trim()+"%");
			 }
			 //按性别查询
			 if(StringUtils.hasText(entity.getGender())){
				 conditionHql.append("and gender=? ");
				 paramList.add(entity.getGender());
			 }
			 //按身份证查询
			 if(StringUtils.hasText(entity.getIdno())){
				 conditionHql.append("and p.idno like ? ");
				 paramList.add("%"+entity.getIdno().trim()+"%");
			 }
			 //按出生日期按开始时间查询
			 if(entity.getBeginTime()!=null){
				 conditionHql.append("and birthDay>=? ");
				 paramList.add(entity.getBeginTime());
			 }
			 //按出生日期按结束时间查询
			 if(entity.getEndTime()!=null){
				 conditionHql.append("and birthDay<=? ");
				 paramList.add(entity.getEndTime());
			 }
			 //按企业名称查询
			 if(entity.getCompany()!=null){
				 //根据企业名称查询
				 if(StringUtils.hasText(entity.getCompany().getName())){
					 conditionHql.append("and com.name like ? ");
					 paramList.add("%"+entity.getCompany().getName().trim());
				 }
				 //根据企业编号查询
				 if(entity.getCompany().getId()!=null){
					 conditionHql.append("and com.id=? ");
					 paramList.add(entity.getCompany().getId());
				 }
				 
			 }
			  
			 //按岗位查询
			 if(StringUtils.hasText(entity.getDutyPost())){
				 conditionHql.append("and p.dutyPost like ? ");
				 paramList.add("%"+entity.getDutyPost()+"%");
			 }
			 if(StringUtils.hasText(entity.getStatisticsColum())){
				 conditionHql.append("and p."+entity.getStatisticsColum().trim()+" is not null ");//根据汇总字段查询
				 conditionHql.append("and (p."+entity.getStatisticsColum().trim()+"Validity<=? ");
				 conditionHql.append("or p."+entity.getStatisticsColum().trim()+"Validity is null) ");
				 paramList.add(GenericsUtils.getPreMonthTime(new Date(), -1));
			 }
		}
		counthql. append(conditionHql);
		selectHql.append(conditionHql);
		selectHql.append(" order by p.createTime");
		return find(selectHql.toString(), counthql.toString(), paramList.toArray(), page);
	}
	

	@Override
	public void delete(Object entityId) throws Exception {
		String []ids=(String[])entityId;
		StringBuilder hql=new StringBuilder("update Member set deleted=? where id in (");
		List<Object>params=new ArrayList<Object>();
		params.add(true);
		for (String id:ids){
			hql.append("?,");
			params.add(Long.valueOf(id));
		}	
		hql.delete(hql.length()-1, hql.length());
		hql.append(")");
		 try {
			executeHQLUpdate(hql.toString(), params.toArray());
		} catch (Exception e) {
			e.printStackTrace();
			throw new BussinessException(DataAssessEnum.UPDATE_ERROR,"更新人员信息失败" );
		}
	}

	 
	 
	@Override
		public boolean exists(Member entity)throws Exception{
			StringBuilder selectHql=new StringBuilder("select count(id) from Member p where deleted=? ");
			List<Object>params=new ArrayList<Object>();
			params.add(false);
			if(entity!=null){
				if(StringUtils.hasText(entity.getName())){//姓名
					selectHql.append("and name=? ");
					params.add(entity.getName().trim());
				}
			 
				if(StringUtils.hasText(entity.getIdno())){//身份证号
					selectHql.append("and idno=? ");
					params.add(entity.getIdno().trim());
				}
				  
				 //按岗位查询
				 if(StringUtils.hasText(entity.getDutyPost())){
					 selectHql.append("and p.dutyPost=? ");
					 params.add(entity.getDutyPost().trim());
				 }
			}
			Query query=this.getSession().createQuery(selectHql.toString());
			for(int i=0;i<params.size();i++){
				query.setParameter(i, params.get(i));
			}
			Long c = Long.valueOf(((Number) query.uniqueResult()).longValue());
			return c.intValue()>0?true:false;
	}

	@Override
	public List<Member> loadMemberList(Member entity) throws Exception {
		StringBuilder hql=new StringBuilder("from Member p left join  fetch p.company com  where p.deleted=? ");
		List<Object>paramList=new ArrayList<Object>();
		paramList.add(false);
		if (entity != null) {
			//按名称查询
			 if(StringUtils.hasText(entity.getName())){
				 hql.append("and name like ? ");
				 paramList.add("%"+entity.getName().trim()+"%");
			 }
			 //按性别查询
			 if(StringUtils.hasText(entity.getGender())){
				 hql.append("and gender=? ");
				 paramList.add(entity.getGender());
			 }
			 //按身份证查询
			 if(StringUtils.hasText(entity.getIdno())){
				 hql.append("and idNo like ? ");
				 paramList.add("%"+entity.getIdno().trim()+"%");
			 }
			 
			 if(entity.getType()!=null){
				 hql.append("and p.type=? ");
				 paramList.add(entity.getType());
			 }
			 
			 //按企业名称查询
			 if(entity.getCompany()!=null){
				 //按编号查询
				 if(entity.getCompany().getId()!=null){
					 hql.append("and com.id=? ");
					 paramList.add(entity.getCompany().getId());
				 }
				 //按名称查询
				 if(StringUtils.hasText(entity.getCompany().getName())){
					 hql.append("and com.name like ? ");
					 paramList.add("%"+entity.getCompany().getName().trim());
				 }
			 }
			  
			//按岗位查询
			 if(StringUtils.hasText(entity.getDutyPost())){
				 hql.append("and p.dutyPost like ? ");
				 paramList.add("%"+entity.getDutyPost()+"%");
			 }
			 
		}
		return findByHql(hql.toString(), paramList.toArray());
	}


	@Override
	public Map<String, Integer> loadExpiredMembers(Long companyId,Map<String,CertSet>cetSetMap)
			throws Exception {
		Map<String,Integer>map=new HashMap<String, Integer>();
		String hql=null;
		if(companyId==null){
			//驾驶证过期汇总统计
		    hql="select count(p.id) from Member p where p.deleted=? and driverLiceseNum is not null and (driverLiceseNumValidity<=? or driverLiceseNumValidity is null)";
			map.put("driverLiceseNum", countByHql(hql,new Object[]{false,GenericsUtils.getPreMonthTime(new Date(), cetSetMap.get("driverLiceseNum")==null?1:cetSetMap.get("driverLiceseNum").getPreMonth().intValue())}).intValue());
			//驾驶资格证过期汇总统计
			hql="select count(p.id) from Member p where p.deleted=? and driverQuaCertNum is not null and (driverQuaCertNumValidity<=? or driverQuaCertNumValidity is null)";
			map.put("driverQuaCertNum", countByHql(hql, new Object[]{false,GenericsUtils.getPreMonthTime(new Date(),cetSetMap.get("driverQuaCertNum")==null?1:cetSetMap.get("driverQuaCertNum").getPreMonth().intValue())}).intValue());
			//押运从业证过期汇总统计
			hql="select count(p.id) from Member p where p.deleted=? and superCargoNum is not null and (superCargoNumValidity<=? or superCargoNumValidity is null)";
			map.put("superCargoNum", countByHql(hql, new Object[]{false,GenericsUtils.getPreMonthTime(new Date(),cetSetMap.get("superCargoNum")==null?1:cetSetMap.get("superCargoNum").getPreMonth().intValue())}).intValue());
			//安管从业资格证过期汇总统计
			hql="select count(p.id) from Member p where p.deleted=? and safetyOfficerNum is not null and (safetyOfficerNumValidity<=? or safetyOfficerNumValidity is null)";
			map.put("safetyOfficerNum", countByHql(hql, new Object[]{false,GenericsUtils.getPreMonthTime(new Date(),cetSetMap.get("safetyOfficerNum")==null?1:cetSetMap.get("safetyOfficerNum").getPreMonth().intValue())}).intValue());
			//主要负责人从业资格证过期汇总统计
			hql="select count(p.id) from Member p where p.deleted=? and mainPersonResNum is not null and (mainPersonResNumValidity<=? or mainPersonResNumValidity is null)";
			map.put("mainPersonResNum", countByHql(hql, new Object[]{false,GenericsUtils.getPreMonthTime(new Date(),cetSetMap.get("mainPersonResNum")==null?1:cetSetMap.get("mainPersonResNum").getPreMonth().intValue())}).intValue());
			//装卸管理员从业资格证过期汇总统计
			hql="select count(p.id) from Member p where p.deleted=? and loadUnloadManNum is not null and (loadUnloadManNumValidity<=? or loadUnloadManNumValidity is null)";
			map.put("loadUnloadManNum", countByHql(hql, new Object[]{false,GenericsUtils.getPreMonthTime(new Date(),cetSetMap.get("loadUnloadManNum")==null?1:cetSetMap.get("loadUnloadManNum").getPreMonth().intValue())}).intValue());
		}else{
			//驾驶证过期汇总统计
		    hql="select count(p.id) from Member p inner join  p.company c where p.deleted=? and c.id=? and p.driverLiceseNum is not null and (p.driverLiceseNumValidity<=? or p.driverLiceseNumValidity is null)";
			map.put("driverLiceseNum", countByHql(hql,new Object[]{false,companyId,GenericsUtils.getPreMonthTime(new Date(), cetSetMap.get("driverLiceseNum")==null?1:cetSetMap.get("driverLiceseNum").getPreMonth().intValue())}).intValue());
			//驾驶资格证过期汇总统计
			hql="select count(p.id) from Member  p inner join  p.company c where p.deleted=? and c.id=? and p.driverQuaCertNum is not null and (p.driverQuaCertNumValidity<=? or p.driverQuaCertNumValidity is null)";
			map.put("driverQuaCertNum", countByHql(hql, new Object[]{false,companyId,GenericsUtils.getPreMonthTime(new Date(),cetSetMap.get("driverQuaCertNum")==null?1:cetSetMap.get("driverQuaCertNum").getPreMonth().intValue())}).intValue());
			//押运从业证过期汇总统计
			hql="select count(p.id) from Member  p inner join  p.company c where p.deleted=? and c.id=? and p.superCargoNum is not null and (p.superCargoNumValidity<=? or p.superCargoNumValidity is null)";
			map.put("superCargoNum", countByHql(hql, new Object[]{false,companyId,GenericsUtils.getPreMonthTime(new Date(),cetSetMap.get("superCargoNum")==null?1:cetSetMap.get("superCargoNum").getPreMonth().intValue())}).intValue());
			//安管从业资格证过期汇总统计
			hql="select count(p.id) from Member  p inner join  p.company c where p.deleted=? and c.id=? and p.safetyOfficerNum is not null and (p.safetyOfficerNumValidity<=? or p.safetyOfficerNumValidity is null)";
			map.put("safetyOfficerNum", countByHql(hql, new Object[]{false,companyId,GenericsUtils.getPreMonthTime(new Date(),cetSetMap.get("safetyOfficerNum")==null?1:cetSetMap.get("safetyOfficerNum").getPreMonth().intValue())}).intValue());
			//主要负责人从业资格证过期汇总统计
			hql="select count(p.id) from Member  p inner join  p.company c where p.deleted=? and c.id=? and p.mainPersonResNum is not null and (p.mainPersonResNumValidity<=? or p.mainPersonResNumValidity is null)";
			map.put("mainPersonResNum", countByHql(hql, new Object[]{false,companyId,GenericsUtils.getPreMonthTime(new Date(),cetSetMap.get("mainPersonResNum")==null?1:cetSetMap.get("mainPersonResNum").getPreMonth().intValue())}).intValue());
			//装卸管理员从业资格证过期汇总统计
			hql="select count(p.id) from Member  p inner join  p.company c where p.deleted=? and c.id=? and p.loadUnloadManNum is not null and (p.loadUnloadManNumValidity<=? or p.loadUnloadManNumValidity is null)";
			map.put("loadUnloadManNum", countByHql(hql, new Object[]{false,companyId,GenericsUtils.getPreMonthTime(new Date(),cetSetMap.get("loadUnloadManNum")==null?1:cetSetMap.get("loadUnloadManNum").getPreMonth().intValue())}).intValue());
		}
		return map;
	}
	
	
	public List<Member> loadMemberOfExpired(Map<String,CertSet>cetSetMap)throws Exception{
		List<Object>params=new ArrayList<Object>();//查询条件参数
		StringBuilder hql=new StringBuilder("from Member p left join  fetch p.company com where p.deleted=? and (");
		params.add(false);
		//驾驶证过期查询条件
		hql.append("(p.driverLiceseNum is not null and (p.driverLiceseNumValidity<=? or p.driverLiceseNumValidity is null)) ");
		params.add(GenericsUtils.getPreMonthTime(new Date(),cetSetMap.get("driverQuaCertNum")==null?1:cetSetMap.get("driverQuaCertNum").getPreMonth().intValue()));
		//驾驶资格证过期查询条件
		hql.append("or (p.driverQuaCertNum is not null and (p.driverQuaCertNumValidity<=? or p.driverQuaCertNumValidity is null)) " );
		params.add(GenericsUtils.getPreMonthTime(new Date(),cetSetMap.get("driverQuaCertNum")==null?1:cetSetMap.get("driverQuaCertNum").getPreMonth().intValue()));
		//押运从业资格证过期查询条件
		hql.append("or (p.superCargoNum is not null and (p.superCargoNumValidity<=? or p.superCargoNumValidity is null))");
		params.add(GenericsUtils.getPreMonthTime(new Date(),cetSetMap.get("superCargoNum")==null?1:cetSetMap.get("superCargoNum").getPreMonth().intValue()));
		//安管从业资格证过期查询条件
		hql.append("or (p.safetyOfficerNum is not null and (p.safetyOfficerNumValidity<=? or p.safetyOfficerNumValidity is null)) ");
		params.add(GenericsUtils.getPreMonthTime(new Date(),cetSetMap.get("safetyOfficerNum")==null?1:cetSetMap.get("safetyOfficerNum").getPreMonth().intValue()));
		//主要负责人从业资格证过期查询条件
		hql.append("or (p.mainPersonResNum is not null and (p.mainPersonResNumValidity<=? or p.mainPersonResNumValidity is null))");
		params.add(GenericsUtils.getPreMonthTime(new Date(),cetSetMap.get("mainPersonResNum")==null?1:cetSetMap.get("mainPersonResNum").getPreMonth().intValue()));
		//装卸管理员从业资格证过期查询条件
		hql.append("or (p.loadUnloadManNum is not null and (p.loadUnloadManNumValidity<=? or p.loadUnloadManNumValidity is null))");
		params.add(GenericsUtils.getPreMonthTime(new Date(),cetSetMap.get("loadUnloadManNum")==null?1:cetSetMap.get("loadUnloadManNum").getPreMonth().intValue()));
		hql.append(")");
		return findByHql(hql.toString(), params.toArray());
	}


	@SuppressWarnings("unchecked")
	@Override
	public List<Member> loadMemberByIds(List<Long> idList) throws Exception {
		StringBuilder selectHql=new StringBuilder("from Member p left join  fetch p.company com where p.deleted=? and  p.id in (:list)");
		Query query=this.getSession().createQuery(selectHql.toString());
		query.setParameter(0, false);
		query.setParameterList("list", idList);
		return query.list();
	}
}
